/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package p3_3_nb;

import java.awt.Point;
import java.sql.*;
import java.util.Random;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author student
 */
public class Main
{
    static Connection con;
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args)
    {
        try {
            try {
                Class.forName("org.postgresql.Driver");
            } catch (ClassNotFoundException e) {
            }
            String url = "jdbc:postgresql://localhost:5432/postgis";
            try {
                con = DriverManager.getConnection(url, "postgres", "hda");
                con.setAutoCommit(false);
                System.out.println("Datenbank verbunden!");
            } catch (SQLException ex) {
                Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
            }

            String dtable = "DROP TABLE IF EXISTS Waylines";
            Statement c;
            c = con.createStatement();
            c.execute(dtable);
            con.commit();

            String ctable = "CREATE TABLE Waylines(id INTEGER primary key, geom geometry)";
            c.execute(ctable);
            con.commit();


            String cmd = "SELECT way_nodes.way_id,ST_X(nodes.geom) as x,ST_Y(nodes.geom) as y FROM ways,nodes,way_nodes WHERE way_nodes.way_id = ways.id AND way_nodes.node_id = nodes.id ORDER BY way_id,sequence_id;";
            Statement s;
            s = con.createStatement();
            ResultSet rs = s.executeQuery(cmd);
            con.commit();

            int wayid = -1;
            boolean first = true;
            int lastid = -1;
            int counter = 0;
            String linestring = "LINESTRING(";
            while(rs.next())
            {
                wayid = rs.getInt("way_id");
                if(lastid == -1 ) lastid = wayid;
                if(wayid == lastid) 
                {
                    if( !first ) linestring += ",";
                    linestring += rs.getString("x") + " " + rs.getString("y");
                    first = false;
                }
                else
                {
                    linestring += ")";
                    String cmd2 = "INSERT INTO Waylines VALUES ("+lastid+",ST_LineFromText('"+linestring+"'));";
                    Statement s2;
                    s2 = con.createStatement();
                    //System.out.println("ID: "+wayid+" CMD: "+cmd2);
                    s2.execute(cmd2);
                    con.commit();
                    counter++;
                    linestring = "LINESTRING("+rs.getString("x")+" "+rs.getString("y")+",";
                    first = true;
                }
                lastid = wayid;
            }

            try {
                con.close();
            } catch (SQLException ex) {
                Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}
